<?php

namespace app\admin\service;

use app\admin\model\PromotePackageLtv;
use app\admin\model\PromotePackagePayLtv;
use app\admin\model\PromotePackageStats;
use app\admin\model\Package;
use app\admin\model\UserPromote;
use app\admin\model\PromotePackagePayRate;
use app\admin\model\PromotePackagePayRemain;
use app\admin\model\PromotePackageRemain;

use DateTime;
use think\exception\DbException;

class PromoteDataService extends BaseService
{
    /**
     * 构造函数
     * LevelService constructor.
     */
    public function __construct()
    {
        $this->model = new PromotePackagePayRate();
    }

    /**
     * 推广员游戏持续付费率
     *
     * @return array
     */
    public function getPromotePackagePayRate(): array
    {
        $param = request()->param();
        $map   = [];
        if(!empty($param['promote_id'])) {
            $map[] = ['promote_id', 'in', $param['promote_id']];
        }
        if (!empty($param['time_range'])) {
            $param['time_range'][0] = implode('', explode('-', $param['time_range'][0]));
            $param['time_range'][1] = implode('', explode('-', $param['time_range'][1]));
            $map[]                  = ['record_date', '>=', $param['time_range'][0]];
            $map[]                  = ['record_date', '<=', $param['time_range'][1]];
        }
        if(!empty($param['package_id'])) {
            $map[] = ['package_id', 'in', $param['package_id']];
        }
        if(!empty($param['register_source'])) {
            $map[] = ['register_source', '=', $param['register_source']];
        }
        $PromotePackagePayRateList = (new PromotePackagePayRate)->where($map)->order('record_date', 'desc');
        $list = $PromotePackagePayRateList
            ->field('id,record_date,promote_id,package_id,register_source,sum(d1) as d1,sum(d2) as d2,sum(d3) as d3,sum(d4) as d4,sum(d5) as d5,sum(d6) as d6,sum(d7) as d7,sum(d14) as d14,sum(d30) as d30,sum(d60) as d60')
            ->group('record_date')
            ->select();
        $arr = [2,3,4,5,6,7,14,30,60];
        foreach ($list as $key => &$val) {
            $val['promote_realname'] = "全部渠道";
            $val['package_name'] = "全部游戏";
            foreach ($arr as $item){
                $val['d'.$item] = empty($val['d1']) ? "0%" : round($val['d' . $item] / $val['d1'] * 100, 2) . "%";
            }
            if(!empty($param['promote_id'])){
                if(count($param['promote_id']) > 1){
                    $val['promote_realname'] = "多个渠道";
                }else{
                    $val['promote_realname'] = (new UserPromote())->whereIn('id', $param['promote_id'])->value('realname');
                }
            }
            if(!empty($param['package_id'])){
                if(count($param['package_id']) > 1){
                    $val['package_name'] = "多个游戏";
                }else{
                    $val['package_name'] = (new Package())->whereIn('id', $param['package_id'])->value('package_name');
                }
            }
        }
        $all = (new PromotePackagePayRate)->where($map)
            ->field('sum(d1) as d1,sum(d2) as d2,sum(d3) as d3,sum(d4) as d4,sum(d5) as d5,sum(d6) as d6,sum(d7) as d7,sum(d14) as d14,sum(d30) as d30,sum(d60) as d60')
            ->find();
        foreach ($arr as $item){
            $all['d'.$item] = empty($all['d1']) ? "0%" : round($all['d' . $item] / $all['d1'] * 100, 2) . "%";
        }
        //返回结果
        return [
            "msg"   => '操作成功',
            "code"  => 0,
            "data"  => $list,
            "count" => 0,
            "all"   => $all
        ];
    }
    /**
     * 推广员游戏付费用户留存统计
     *
     * @return array
     */
    public function getPromotePackagePayRemain(): array
    {
        $param = request()->param();
        $map   = [];
        if(!empty($param['promote_id'])) {
            $map[] = ['promote_id', 'in', $param['promote_id']];
        }
        if (!empty($param['time_range'])) {
            $param['time_range'][0] = implode('', explode('-', $param['time_range'][0]));
            $param['time_range'][1] = implode('', explode('-', $param['time_range'][1]));
            $map[]                  = ['record_date', '>=', $param['time_range'][0]];
            $map[]                  = ['record_date', '<=', $param['time_range'][1]];
        }
        if(!empty($param['package_id'])) {
            $map[] = ['package_id', 'in', $param['package_id']];
        }
        if(!empty($param['register_source'])) {
            $map[] = ['register_source', '=', $param['register_source']];
        }

        $PromotePackagePayRemainList = (new PromotePackagePayRemain)->where($map)
            ->order('record_date', 'desc');
        $list = $PromotePackagePayRemainList
            ->field('id,record_date,promote_id,package_id,register_source,sum(pay_num) as pay_num,sum(d2) as d2,sum(d3) as d3,sum(d4) as d4,sum(d5) as d5,sum(d6) as d6,sum(d7) as d7,sum(d14) as d14,sum(d30) as d30,sum(d60) as d60,sum(d90) as d90,sum(d180) as d180,sum(d360) as d360')
            ->group('record_date')
            ->select();
        $arr = [2,3,4,5,6,7,14,30,60,90,180,360];
        foreach ($list as $key => &$val) {
            $val['promote_realname'] = "全部渠道";
            $val['package_name'] = "全部游戏";
            if(!empty($param['promote_id'])){
                if(count($param['promote_id']) > 1){
                    $val['promote_realname'] = "多个渠道";
                }else{
                    $val['promote_realname'] = (new UserPromote())->whereIn('id', $param['promote_id'])->value('realname');
                }
            }
            if(!empty($param['package_id'])){
                if(count($param['package_id']) > 1){
                    $val['package_name'] = "多个游戏";
                }else{
                    $val['package_name'] = (new Package())->whereIn('id', $param['package_id'])->value('package_name');
                }
            }
            foreach ($arr as $item) {
                $val['d' . $item] = empty($val['pay_num']) ? "0%" : round($val['d' . $item] / $val['pay_num'] * 100, 2) . "%";
            }
        }
        $all = (new PromotePackagePayRemain)->where($map)
            ->field('sum(pay_num) as pay_num,sum(d2) as d2,sum(d3) as d3,sum(d4) as d4,sum(d5) as d5,sum(d6) as d6,sum(d7) as d7,sum(d14) as d14,sum(d30) as d30,sum(d60) as d60,sum(d90) as d90,sum(d180) as d180,sum(d360) as d360')
            ->find();
        foreach ($arr as $item){
            $all['d'.$item] = empty($all['pay_num']) ? "0%" : round($all['d' . $item] / $all['pay_num'] * 100, 2) . "%";
        }
        //返回结果
        return [
            "msg"   => '操作成功',
            "code"  => 0,
            "data"  => $list,
            "count" => 0,
            "all"   => $all
        ];
    }

    /**
     * 推广员游戏用户留存统计
     * @return array
     */
    public function getPromotePackageRemain(): array
    {
        $param = request()->param();
        $map   = [];
        if(!empty($param['promote_id'])) {
            $map[] = ['promote_id', 'in', $param['promote_id']];
        }
        if (!empty($param['time_range'])) {
            $param['time_range'][0] = implode('', explode('-', $param['time_range'][0]));
            $param['time_range'][1] = implode('', explode('-', $param['time_range'][1]));
            $map[]                  = ['record_date', '>=', $param['time_range'][0]];
            $map[]                  = ['record_date', '<=', $param['time_range'][1]];
        }
        if(!empty($param['package_id'])) {
            $map[] = ['package_id', 'in', $param['package_id']];
        }
        if(!empty($param['register_source'])) {
            $map[] = ['register_source', '=', $param['register_source']];
        }

        $PromotePackageRemainList = (new PromotePackageRemain)->where($map)
            ->order('record_date', 'desc');
        $list = $PromotePackageRemainList
            ->field('id,record_date,promote_id,package_id,register_source,sum(register_num) as register_num,sum(d2) as d2,sum(d3) as d3,sum(d4) as d4,sum(d5) as d5,sum(d6) as d6,sum(d7) as d7,sum(d14) as d14,sum(d30) as d30,sum(d60) as d60,sum(d90) as d90,sum(d180) as d180,sum(d360) as d360')
            ->group('record_date')
            ->select();
        $arr = [2,3,4,5,6,7,14,30,60,90,180,360];
        foreach ($list as $key => &$val) {
            $val['promote_realname'] = "全部渠道";
            $val['package_name'] = "全部游戏";
            if(!empty($param['promote_id'])){
                if(count($param['promote_id']) > 1){
                    $val['promote_realname'] = "多个渠道";
                }else{
                    $val['promote_realname'] = (new UserPromote())->whereIn('id', $param['promote_id'])->value('realname');
                }
            }
            if(!empty($param['package_id'])){
                if(count($param['package_id']) > 1){
                    $val['package_name'] = "多个游戏";
                }else{
                    $val['package_name'] = (new Package())->whereIn('id', $param['package_id'])->value('package_name');
                }
            }
            foreach ($arr as $item) {
                $val['d' . $item] = empty($val['register_num']) ? "0%" : round($val['d' . $item] / $val['register_num'] * 100, 2) . "%";
            }
        }
        $all = (new PromotePackageRemain)->where($map)
            ->field('sum(register_num) as register_num,sum(d2) as d2,sum(d3) as d3,sum(d4) as d4,sum(d5) as d5,sum(d6) as d6,sum(d7) as d7,sum(d14) as d14,sum(d30) as d30,sum(d60) as d60,sum(d90) as d90,sum(d180) as d180,sum(d360) as d360')
            ->find();
        foreach ($arr as $item){
            $all['d'.$item] = empty($all['register_num']) ? "0%" : round($all['d' . $item] / $all['register_num'] * 100, 2) . "%";
        }
        //返回结果
        return [
            "msg"   => '操作成功',
            "code"  => 0,
            "data"  => $list,
            "count" => 0,
            "all"   => $all
        ];
    }

    /**
     * 数据总览
     * @return array
     * @throws DbException
     */
    public function getPromotePackageAll(): array
    {
        $param = request()->param();
        $type = $param['type'];
        $map   = [];
        if(!empty($param['promote_id'])) {
            $map[] = ['promote_id', 'in', $param['promote_id']];
        }
        if (!empty($param['time_range'])) {
            $param['time_range'][0] = implode('', explode('-', $param['time_range'][0]));
            $param['time_range'][1] = implode('', explode('-', $param['time_range'][1]));
            $map[]                  = ['record_date', '>=', $param['time_range'][0]];
            $map[]                  = ['record_date', '<=', $param['time_range'][1]];
        }
        if(!empty($param['package_id'])) {
            $map[] = ['package_id', 'in', $param['package_id']];
        }
        if(!empty($param['register_source'])) {
            $map[] = ['register_source', '=', $param['register_source']];
        }
        $order = 'record_date desc';
        // 游戏总览
        if($type == 1){
            $group = 'package_id';
            $order = 'pay_amount desc';
            $package = (new Package())->column('package_name', 'id');
        }
        // 推广渠道总览
        if($type == 2){
            $group = 'promote_id';
            $order = 'pay_amount desc';
            $promote = (new UserPromote())->column('realname', 'id');
        }
        // 每日总览
        if($type == 3){
            $group = 'record_date';
        }
        $PromotePackageStatsList = (new PromotePackageStats)
            ->field("id,record_date,promote_id,package_id,sum(register_num) as register_num,sum(pay_amount) as pay_amount,sum(login_num) as login_num,sum(register_device_num) as register_device_num,sum(pay_user_num) as pay_user_num,sum(login_device_num) as login_device_num,sum(register_role_num) as register_role_num,sum(new_pay_user_num) as new_pay_user_num,sum(new_amount) as new_amount")
            ->where($map)
            ->group($group)
            ->order($order)
            ->page(PAGE, PERPAGE)
            ->select();
        $countSql = (new PromotePackageStats)
            ->field("id,record_date,promote_id,package_id,sum(register_num) as register_num,sum(pay_amount) as pay_amount,sum(login_num) as login_num,sum(register_device_num) as register_device_num,sum(pay_user_num) as pay_user_num,sum(login_device_num) as login_device_num,sum(register_role_num) as register_role_num,sum(new_pay_user_num) as new_pay_user_num,sum(new_amount) as new_amount")
            ->where($map)
            ->group($group)
            ->order($order)
            ->distinct(true)
            ->buildSql();

        $count = (new PromotePackageStats)->table($countSql)->alias('aa')->count();

        foreach ($PromotePackageStatsList as $key => &$val) {
            $val['darpu'] = $val['login_num'] > 0 ? round($val['pay_amount']/$val['login_num'], 2) : '0';
            $val['arppu'] = $val['pay_user_num'] > 0 ? round($val['pay_amount']/$val['pay_user_num'], 2) : '0';
            $val['pay_ratio'] = $val['login_num'] > 0 ? round(($val['pay_user_num']/$val['login_num']) * 100, 2).'%' : '0%';
            $val['new_pay_ratio'] = $val['register_num'] > 0 ? round(($val['new_pay_user_num']/$val['register_num']) * 100, 2).'%' : '0%';
            if($type == 1){
                $val['package_name'] = isset($package[$val['package_id']]) ? $package[$val['package_id']] : "";
                if($val['package_id'] == 9999998){
                    $val['package_name'] = '游戏盒子(IOS)';
                }
                if ($val['package_id'] == 9999999){
                    $val['package_name'] = '游戏盒子(安卓)';
                }
            }
            if($type == 2){
                $val['promote_realname'] = isset($promote[$val['promote_id']]) ? $promote[$val['promote_id']]: "";
            }
            if($type == 3){
                $val['promote_realname'] = '全部渠道';
                $val['package_name'] = "全部游戏";
                if(!empty($param['promote_id'])){
                    if(count($param['promote_id']) > 1){
                        $val['promote_realname'] = "多个渠道";
                    }else{
                        $val['promote_realname'] = (new UserPromote())->whereIn('id', $param['promote_id'])->value('realname');
                    }
                }
                if(!empty($param['package_id'])){
                    if(count($param['package_id']) > 1){
                        $val['package_name'] = "多个游戏";
                    }else{
                        $val['package_name'] = (new Package())->whereIn('id', $param['package_id'])->value('package_name');
                    }
                }
            }
        }
        $all = (new PromotePackageStats)->where($map)
            ->field("sum(register_num) as register_num,sum(pay_amount) as pay_amount,sum(login_num) as login_num,sum(register_device_num) as register_device_num,sum(pay_user_num) as pay_user_num,sum(login_device_num) as login_device_num,sum(register_role_num) as register_role_num,sum(new_pay_user_num) as new_pay_user_num,sum(new_amount) as new_amount")
            ->find();
        $all['darpu'] = $all['login_num'] > 0 ? round($all['pay_amount']/$all['login_num'], 2) : '0';
        $all['arppu'] = $all['pay_user_num'] > 0 ? round($all['pay_amount']/$all['pay_user_num'], 2) : '0';
        $all['pay_ratio'] = $all['login_num'] > 0 ? round(($all['pay_user_num']/$all['login_num']) * 100, 2).'%' : '0%';
        $all['new_pay_ratio'] = $all['register_num'] > 0 ? round(($all['new_pay_user_num']/$all['register_num']) * 100, 2).'%' : '0%';
        //返回结果
        return [
            "msg"   => '操作成功',
            "code"  => 0,
            "data"  => $PromotePackageStatsList,
            "count" => $count,
            "all"   => $all
        ];
    }


    /**
     * 推广员游戏付费用户ltv统计
     *
     * @return array
     */
    public function getPromotePackagePayLtv(): array
    {
        $param = request()->param();
        $map   = [];
        if(!empty($param['promote_id'])) {
            $map[] = ['promote_id', 'in', $param['promote_id']];
        }
        if (!empty($param['time_range'])) {
            $param['time_range'][0] = implode('', explode('-', $param['time_range'][0]));
            $param['time_range'][1] = implode('', explode('-', $param['time_range'][1]));
            $map[]                  = ['record_date', '>=', $param['time_range'][0]];
            $map[]                  = ['record_date', '<=', $param['time_range'][1]];
        }
        if(!empty($param['package_id'])) {
            $map[] = ['package_id', 'in', $param['package_id']];
        }
        if(!empty($param['register_source'])) {
            $map[] = ['register_source', '=', $param['register_source']];
        }

        $PromotePackagePayLtvList = (new PromotePackagePayLtv())->where($map)
            ->order('record_date', 'desc');
        $list = $PromotePackagePayLtvList
            ->field('id,record_date,promote_id,package_id,register_source,sum(register_num) as register_num,sum(d_all) as d_all,sum(d1) as d1,sum(d2) as d2,sum(d3) as d3,sum(d4) as d4,sum(d5) as d5,sum(d6) as d6,sum(d7) as d7,sum(d14) as d14,sum(d30) as d30,sum(d45) as d45,sum(d60) as d60,sum(d90) as d90,sum(d120) as d120,sum(d180) as d180,sum(d360) as d360')
            ->group('record_date')
            ->select();
        $arr = [1,2,3,4,5,6,7,14,30,45,60,90,120,180,360];
        foreach ($list as $key => &$val) {
            $val['promote_realname'] = "全部渠道";
            $val['package_name'] = "全部游戏";
            if(!empty($param['promote_id'])){
                if(count($param['promote_id']) > 1){
                    $val['promote_realname'] = "多个渠道";
                }else{
                    $val['promote_realname'] = (new UserPromote())->whereIn('id', $param['promote_id'])->value('realname');
                }
            }
            if(!empty($param['package_id'])){
                if(count($param['package_id']) > 1){
                    $val['package_name'] = "多个游戏";
                }else{
                    $val['package_name'] = (new Package())->whereIn('id', $param['package_id'])->value('package_name');
                }
            }
            $val['d_all'] = empty($val['register_num']) ? "0" : round($val['d_all'] / $val['register_num'], 2) ;
            $day = diffTimeRetrunDay(strtotime($val['record_date']), strtotime(date("Y-m-d")));
            $day = $day + 1;
            foreach ($arr as $item) {
                if($day > $item){
                    $val['d' . $item] = empty($val['register_num']) ? "0" : round($val['d' . $item] / $val['register_num'], 2);
                }else{
                    $val['d' . $item] = 0;
                }
            }
        }
        $all = (new PromotePackagePayLtv)->where($map)
            ->field('sum(register_num) as register_num,sum(d_all) as d_all,sum(d1) as d1,sum(d2) as d2,sum(d3) as d3,sum(d4) as d4,sum(d5) as d5,sum(d6) as d6,sum(d7) as d7,sum(d14) as d14,sum(d30) as d30,sum(d45) as d45,sum(d60) as d60,sum(d90) as d90,sum(d120) as d120,sum(d180) as d180,sum(d360) as d360')
            ->find();
        foreach ($arr as $item){
            $all['d'.$item] = empty($all['register_num']) ? "0" : round($all['d' . $item] / $all['register_num'], 2);
        }
        $all['d_all'] = empty($all['register_num']) ? "0" : round($all['d_all'] / $all['register_num'] * 100, 2);
        //返回结果
        return [
            "msg"   => '操作成功',
            "code"  => 0,
            "data"  => $list,
            "count" => 0,
            "all"   => $all
        ];
    }

    /**
     * 推广员游戏用户ltv统计
     *
     * @return array
     */
    public function getPromotePackageLtv(): array
    {
        $param = request()->param();
        $map   = [];
        if(!empty($param['promote_id'])) {
            $map[] = ['promote_id', 'in', $param['promote_id']];
        }
        if (!empty($param['time_range'])) {
            $param['time_range'][0] = implode('', explode('-', $param['time_range'][0]));
            $param['time_range'][1] = implode('', explode('-', $param['time_range'][1]));
            $map[]                  = ['record_date', '>=', $param['time_range'][0]];
            $map[]                  = ['record_date', '<=', $param['time_range'][1]];
        }
        if(!empty($param['package_id'])) {
            $map[] = ['package_id', 'in', $param['package_id']];
        }
        if(!empty($param['register_source'])) {
            $map[] = ['register_source', '=', $param['register_source']];
        }

        $PromotePackageLtvList = (new PromotePackageLtv())->where($map)
            ->order('record_date', 'desc');
        $list = $PromotePackageLtvList
            ->field('id,record_date,promote_id,package_id,register_source,sum(register_num) as register_num,sum(d_all) as d_all,sum(d1) as d1,sum(d2) as d2,sum(d3) as d3,sum(d4) as d4,sum(d5) as d5,sum(d6) as d6,sum(d7) as d7,sum(d14) as d14,sum(d30) as d30,sum(d45) as d45,sum(d60) as d60,sum(d90) as d90,sum(d120) as d120,sum(d180) as d180,sum(d360) as d360')
            ->group('record_date')
            ->select();
        $arr = [1,2,3,4,5,6,7,14,30,45,60,90,120,180,360];
        foreach ($list as $key => &$val) {
            $val['promote_realname'] = "全部渠道";
            $val['package_name'] = "全部游戏";
            if(!empty($param['promote_id'])){
                if(count($param['promote_id']) > 1){
                    $val['promote_realname'] = "多个渠道";
                }else{
                    $val['promote_realname'] = (new UserPromote())->whereIn('id', $param['promote_id'])->value('realname');
                }
            }
            if(!empty($param['package_id'])){
                if(count($param['package_id']) > 1){
                    $val['package_name'] = "多个游戏";
                }else{
                    $val['package_name'] = (new Package())->whereIn('id', $param['package_id'])->value('package_name');
                }
            }
            $val['d_all'] = empty($val['register_num']) ? "0" : round($val['d_all'] / $val['register_num'], 2);

            $day = diffTimeRetrunDay(strtotime($val['record_date']), strtotime(date("Y-m-d")));
            $day = $day + 1;
            foreach ($arr as $item) {
                if($day > $item){
                    $val['d' . $item] = empty($val['register_num']) ? "0" : round($val['d' . $item] / $val['register_num'], 2);
                }else{
                    $val['d' . $item] = 0;
                }
            }
        }
        $all = (new PromotePackageLtv)->where($map)
            ->field('sum(register_num) as register_num,sum(d_all) as d_all,sum(d1) as d1,sum(d2) as d2,sum(d3) as d3,sum(d4) as d4,sum(d5) as d5,sum(d6) as d6,sum(d7) as d7,sum(d14) as d14,sum(d30) as d30,sum(d45) as d45,sum(d60) as d60,sum(d90) as d90,sum(d120) as d120,sum(d180) as d180,sum(d360) as d360')
            ->find();
        foreach ($arr as $item){
            $all['d'.$item] = empty($all['register_num']) ? "0" : round($all['d' . $item] / $all['register_num'], 2);
        }
        $all['d_all'] = empty($all['register_num']) ? "0" : round($all['d_all'] / $all['register_num'], 2);
        //返回结果
        return [
            "msg"   => '操作成功',
            "code"  => 0,
            "data"  => $list,
            "count" => 0,
            "all"   => $all
        ];
    }



}
